package commSql

import (
	"gitee.com/fierce_wolf/go-fox-edge-common/commUtil/Method"
	"strconv"
)

type subSqlBuilder struct {
}
type sqlBuilder struct {
	SubBuilder subSqlBuilder
}

func (e *sqlBuilder) BuildSelectCount(sql string) string {

	sb := "        SELECT COUNT(1)                    "
	sb += "        FROM (" + sql + ") t1              "

	return sb
}

// BuildSelectPage 生成分页查询语句
// 参数说明：
// tableName 数据库表名称，例如，tb_book
// vFields 限定返回哪些字段，可以为nil
// cFields 限定返回字段的数据类型，可以为nil
// mtsql 主表的查询子句，类似SELECT s1.id FROM tb_book s1  ORDER BY s1.id DESC的语句，注意别名用s1这类命名，不要用t1命名
func (e *sqlBuilder) BuildSelectPage(tableName string, vFields []string, cFields []string, mtsql string, pageNmu int, pageSize int) string {
	pageIndex := (pageNmu - 1) * pageSize

	sb := ""

	sbf := ""
	if Method.HasEmpty(vFields, cFields) {
		sbf += "t4.* ,"
	}
	if !Method.HasEmpty(vFields) {
		for _, field := range vFields {
			sbf += "t4."
			sbf += field
			sbf += ","
		}
	}
	if !Method.HasEmpty(cFields) {
		for _, field := range vFields {
			sbf += field
			sbf += ","
		}
	}

	fs := sbf[0 : len(sbf)-1]

	sb += "        SELECT " + fs + "                          "
	sb += "        FROM " + tableName + " t4                  "
	sb += "        RIGHT JOIN                                 "
	sb += "        ( " + mtsql + " LIMIT " + strconv.Itoa(pageIndex) + "," + strconv.Itoa(pageSize) + " ) t3                       "
	sb += "        ON t4.id = t3.id                           "

	return sb
}

func (e *sqlBuilder) BuildSelectList(tableName string, vFields []string, cFields []string, mtsql string) string {
	sb := ""

	sbf := ""
	if Method.HasEmpty(vFields, cFields) {
		sbf += "t4.* ,"
	}
	if !Method.HasEmpty(vFields) {
		for _, field := range vFields {
			sbf += "t4."
			sbf += field
			sbf += ","
		}
	}
	if !Method.HasEmpty(cFields) {
		for _, field := range vFields {
			sbf += field
			sbf += ","
		}
	}

	fs := sbf[0 : len(sbf)-1]

	sb += "        SELECT " + fs + "                          "
	sb += "        FROM " + tableName + " t4                  "
	sb += "        RIGHT JOIN                                 "
	sb += "        ( " + mtsql + " ) t3                       "
	sb += "        ON t4.id = t3.id                           "

	return sb
}

func (e *subSqlBuilder) BuildOrderBy(tableName string, filter string, orderField, orderMode string) string {
	where := ""
	if filter != "" {
		where = "WHERE (" + filter + ")"
	}

	if orderField == "" {
		orderField = "id"
	}

	ASC := "ASC"
	if orderMode == "DESC" {
		ASC = "DESC"
	}

	// 主表相关的子查询
	return "SELECT s1.id FROM " + tableName + " s1 " + where + " ORDER BY s1." + orderField + " " + ASC
}

func (e *subSqlBuilder) BuildLeftJoinOnGroupBy(tableName string, groupByTableName, groupByJoinOnId string, orderMode string) string {
	ASC := "ASC"
	if orderMode == "DESC" {
		ASC = "DESC"
	}

	// 主表相关的子查询
	return "SELECT s2.id FROM " + tableName + " s2 LEFT JOIN ( SELECT " + groupByJoinOnId + ", COUNT(*) AS cnt FROM " + groupByTableName + " GROUP BY " + groupByJoinOnId + ") s1 ON s2.id = s1." + groupByJoinOnId + " ORDER BY s1.cnt " + ASC
}
